Duplicate oid and primary key values - Mailing list pgsql-general

I have a table in a PG 7.4.1 database with 380 duplicate rows,
including duplicate oid and primary key values.  Looking through our
backups, the duplicates did not exist before Friday, 02/06/2004.  I'm
assuming neither pg_dumpall nor restoring from a pg_dumpall file will
eliminate such duplicates.  We upgraded from 7.3.4 to 7.4.1 on
02/02/2004.

What can cause these duplicates?

The server has had several system crashes over the past few days and weeks.

Below is my session with the DB showing an example of the duplicates,
the table structure, and trigger functions.

cos=> select oid, recordnumber from client where recordnumber = 10970;
    oid   | recordnumber
---------+--------------
  2427408 |        10970
(1 row)

cos=> select oid, recordnumber from client where recordnumber < 10971
and recordnumber > 10969;
    oid   | recordnumber
---------+--------------
  2427408 |        10970
  2427408 |        10970
(2 rows)

cos=> \d client
                                                Table "public.client"
         Column        |          Type           |
Modifiers
----------------------+-------------------------+------------------------------------------------------------------
  recordnumber         | integer                 | not null default
nextval('public.client_recordnumber_seq'::text)
  recordnumber_display | integer                 | not null
  access               | text                    |
  add1                 | character varying(255)  |
  add2                 | character varying(255)  |
  add_id               | integer                 |
  age                  | character varying(255)  |
  akas                 | character varying(255)  |
  besttime             | character varying(255)  |
  birthdate            | character varying(255)  |
  city                 | character varying(255)  |
  country              | character varying(255)  |
  creation_date        | date                    | not null default now()
  creation_time        | time without time zone  | not null default now()
  custom1              | character varying(255)  |
  custom10             | character varying(255)  |
  custom2              | character varying(255)  |
  custom3              | character varying(255)  |
  custom4              | character varying(255)  |
  custom5              | character varying(255)  |
  custom6              | character varying(255)  |
  custom7              | character varying(255)  |
  custom8              | character varying(255)  |
  custom9              | character varying(255)  |
  disability           | character varying(255)  |
  edit_date            | date                    | not null default now()
  edit_time            | time without time zone  | not null default now()
  edit_id              | integer                 |
  education            | character varying(255)  |
  email                | character varying(255)  |
  employer             | character varying(255)  |
  ethnicity            | character varying(1)    |
  extra1               | character varying(255)  |
  extra8               | character varying(255)  |
  first                | character varying(255)  |
  gender               | character varying(255)  |
  incomelevel          | character varying(255)  |
  incomenotes          | character varying(255)  |
  insurance            | character varying(255)  |
  last                 | character varying(255)  |
  location             | character varying(255)  |
  maritalstatus        | character varying(255)  |
  nochildren           | character varying(255)  |
  otherphone           | character varying(255)  |
  own_id               | integer                 |
  phhome               | character varying(255)  |
  phwork               | character varying(255)  |
  prefcontact          | character varying(255)  |
  primarylang          | character varying(255)  |
  referredby           | character varying(255)  |
  restrictorg_id       | integer                 |
  serverid             | character(5)            | not null
  ssno                 | character varying(255)  |
  state                | character varying(255)  |
  status               | integer                 |
  title                | character varying(255)  |
  transportation       | character varying(255)  |
  zip                  | character varying(255)  |
  extra2               | character varying(255)  |
  temp_extra8          | character varying(255)  |
  extra10              | character varying(255)  |
  authorize            | character varying(1000) |
Indexes:
     "client_pkey" primary key, btree (recordnumber)
     "idx_client_recordnum_display" unique, btree
(upper_concat(serverid, recordnumber_display))
     "idx_client_first" btree ("first")
     "idx_client_last" btree ("last")
     "idx_client_restrictorg_id" btree (restrictorg_id)
     "idx_client_serverid" btree (serverid)
     "idx_client_status" btree (status)
Foreign-key constraints:
     "$1" FOREIGN KEY (restrictorg_id) REFERENCES
agency_dbs(record_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
     tgr_client_edit_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE
PROCEDURE fnc_edit_date()
     tgr_client_edit_time BEFORE UPDATE ON client FOR EACH ROW EXECUTE
PROCEDURE fnc_edit_time()
     tgr_client_recordnumber_display BEFORE INSERT ON client FOR EACH
ROW EXECUTE PROCEDURE fnc_recordnumber_display()

cos=> \connect - postgres
You are now connected as new user "postgres".
cos=# select prosrc from pg_proc where proname = 'fnc_recordnumber_display';
                                         prosrc
---------------------------------------------------------------------------------------
  DECLARE
         BEGIN
                 new.recordnumber_display = new.recordnumber;
                 RETURN new;
         END;
(1 row)

cos=# select prosrc from pg_proc where proname = 'fnc_edit_date';
                        prosrc
----------------------------------------------------
  BEGIN
                 new.edit_date := 'now';
         RETURN new;
         END;
(1 row)

cos=# select prosrc from pg_proc where proname = 'fnc_edit_time';
                        prosrc
----------------------------------------------------
  BEGIN
                 new.edit_time := 'now';
         RETURN new;
         END;
(1 row)


--

Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.